package cemetery.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import cemetery.bean.order;
import cemetery.dao.BaseDao;
import cemetery.dao.OrderDao;

public class OrderDaoImpl extends BaseDao implements OrderDao {

	@Override
	public List<order> getList(order news) {
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		List<order> list=new ArrayList<>();
		try {
			conn=getconnection();
			String sql=" select id,materiel,number,price,customer,totalprice,shifoufukuan "
			+" from `order` where 1=1 ";
			if (news!=null && news.getId()!=0) {
				sql=sql+" and id='"+news.getId()+"' ";
			}
			if (news!=null && news.getMateriel()!=null&& !"".equals(news.getMateriel())) {
				sql=sql+" and materiel like '%"+news.getMateriel()+"%' ";
			}
			stmt=conn.prepareStatement(sql);
			rs=stmt.executeQuery();
			while (rs.next()) {
				order n=new order();
				n.setId(rs.getInt(1));
				n.setMateriel(rs.getString(2));
				n.setNumber(rs.getInt(3));
				n.setPrice(rs.getInt(4));
				n.setCustomer(rs.getString(5));
				n.setTotalprice(rs.getInt(6));
				n.setShifoufukuan(rs.getString(7));
				list.add(n);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			this.closeAll(conn, stmt, rs);
		}
		return list;
	}

	@Override
	public int add(order news) {
		String psql="insert into `order`(materiel,number,price,customer,totalprice)values(?,?,?,?,?)";
		Object[]params= {news.getMateriel(),news.getNumber(),news.getPrice(),news.getCustomer(),news.getTotalprice()};
		return this.executeUpdate(psql, params);
	}

	@Override
	public int update(order news) {
		String psql="update  `order` set materiel=?,number=?,price=?,customer=?,totalprice=? where id=?";
		Object[]params= {news.getMateriel(),news.getNumber(),news.getPrice(),news.getCustomer(),news.getTotalprice(),news.getId()};
		return this.executeUpdate(psql, params);
	}

	@Override
	public int deleteById(int id) {
		String psql="delete from `order` where id=?";
		Object[]params= {id};
		return this.executeUpdate(psql, params);
	}
	public int getTotalCount(order news) {
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		int totalCount=0;
		try {
			conn=getconnection();
			String sql="select count(1) from `order` where 1=1 ";
			if (news!=null && news.getId()!=0) {
				sql=sql+" and id='"+news.getId()+"' ";
			}
			if (news!=null && news.getMateriel()!=null&& !"".equals(news.getMateriel())) {
				sql=sql+" and materiel like '%"+news.getMateriel()+"%' ";
			}
			stmt=conn.prepareStatement(sql);
			rs=stmt.executeQuery();
			while (rs.next()) {
				totalCount=rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			this.closeAll(conn, stmt, rs);
		}
		return totalCount;
	}

	@Override
	public List<order> setPageList(order news, int pageIndex, int pageSize) {
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		List<order> list=new ArrayList<>();
		try {
			conn=getconnection();
			String sql="select id,materiel,number,price,customer,totalprice "
			+" from `order` where 1=1 ";
			if (news!=null && news.getId()!=0) {
				sql=sql+" and id='"+news.getId()+"' ";
			}
			if (news!=null && news.getMateriel()!=null&& !"".equals(news.getMateriel())) {
				sql=sql+" and materiel like '%"+news.getMateriel()+"%' ";
			}
			sql=sql+" limit "+(pageIndex-1)*pageSize+","+pageSize;
			stmt=conn.prepareStatement(sql);
			rs=stmt.executeQuery();
			while (rs.next()) {
				order n=new order();
				n.setId(rs.getInt(1));
				n.setMateriel(rs.getString(2));
				n.setNumber(rs.getInt(3));
				n.setPrice(rs.getInt(4));
				n.setCustomer(rs.getString(5));
				n.setTotalprice(rs.getInt(6));
				
				list.add(n);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			this.closeAll(conn, stmt, rs);
		}
		return list;
	}
}
